﻿using System;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using Application = Microsoft.Office.Interop.Excel.Application;

namespace IGM.Framework.Commons
{
    public static class ExcelUtils
    {
        /// <summary>
        /// Import data form excel file
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="view"></param>
        /// <param name="from">Like A1</param>
        /// <param name="to">Like C100</param>
        /// <param name="selectColumns"></param>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public static bool ImportExcelOnRange(string filename,DataGridView view, string from, string to, string selectColumns, int sheet)
        {
            Application excelApp = null;
            Workbook workbook = null;
            Worksheet worksheet = null;
            try
            {
                excelApp = new Application();
                workbook = excelApp.Workbooks.Open(
                    filename, 0, true, 5, string.Empty, string.Empty, true, XlPlatform.xlWindows,
                    "\t", false, false, 0, true, 1, 0);
                worksheet = (Worksheet)workbook.Worksheets.Item[sheet];
                var range = worksheet.Range[from, to];

                int[] columns;
                if (string.IsNullOrEmpty(selectColumns))
                {
                    columns = new int[view.ColumnCount];
                    for (var i = 0; i < columns.Length; i++)
                        columns[i] = (i + 1);
                }
                else
                {
                    var sColumns = selectColumns.Split(',');
                    columns = new int[sColumns.Length];
                    for (var i = 0; i < columns.Length; i++)
                    {
                        columns[i] = Convert.ToInt32(sColumns[i]);
                    }
                }

                for (var row = 1; row <= range.Rows.Count; row++)
                {
                    var newRow = view.Rows.Add();
                    for (var col = 0; col < view.ColumnCount; col++)
                    {
                        view[col, newRow].Value = ((Range)range.Cells[row, columns[col]]).Value2;
                    }
                }

                workbook.Close(false);
                excelApp.Quit();
                return true;
            }
            catch
            {
                if (workbook != null)
                {
                    workbook.Close(false);
                    excelApp.Quit();
                }
                return false;
            }
            finally
            {
                ReleaseObject(worksheet);
                ReleaseObject(workbook);
                ReleaseObject(excelApp);
            }
        }

        /// <summary>
        /// Export data to excel file
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="view"></param>
        /// <param name="from">Like A1</param>
        /// <param name="to">Like C100</param>
        /// <returns></returns>
        public static bool ExportExcelOnRange
            (string filename, DataGridView view, string from, string to)
        {
            try
            {
                var objOpt = Missing.Value;
                // Start a new workbook in Excel.
                var excelApp = new Application();
                var workbook = (_Workbook)(excelApp.Workbooks.Add(objOpt));
                var worksheet = (_Worksheet)(workbook.Worksheets.Item[1]);

                var objData = new Object[view.Rows.Count, view.Columns.Count];
                for (var i = 0; i < view.Rows.Count; i++)
                {
                    for (var j = 0; j < view.Columns.Count; j++)
                    {
                        objData[i, j] = view[j, i].Value;
                    }
                }
                var objRange = worksheet.Range[from, objOpt];
                objRange = objRange.Resize[view.Rows.Count, view.Columns.Count];
                objRange.Value = objData;

                // Save the Workbook and quit Excel.
                workbook.SaveAs(filename, objOpt, objOpt,
                               objOpt, objOpt, objOpt, XlSaveAsAccessMode.xlNoChange,
                               objOpt, objOpt, objOpt, objOpt);
                workbook.Close(false, objOpt, objOpt);
                excelApp.Quit();

                ReleaseObject(worksheet);
                ReleaseObject(workbook);
                ReleaseObject(excelApp);

                return false;
            }
            catch
            {
                return false;
            }
        }

        private static void ReleaseObject(object obj)
        {
            try { Marshal.ReleaseComObject(obj); }
            finally { GC.Collect(); }
        }
    }
}